package com.example.jpa.repository.impl;

import com.example.jpa.entity.QClazzInf;
import com.example.jpa.entity.QStudentInf;
import com.example.jpa.entity.StudentInf;
import com.example.jpa.entity.dto.JoinInfDTO;
import com.example.jpa.entity.dto.StudentInfDTO;
import com.example.jpa.repository.CustomStudentInfRepository;
import com.example.jpa.repository.StudentInfRepository;
import com.querydsl.core.BooleanBuilder;
import com.querydsl.core.QueryResults;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.Expressions;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @author qilongxin
 * @date 2021/8/26 19:05
 */
public class CustomStudentInfRepositoryImpl implements CustomStudentInfRepository {
    @Autowired
    JPAQueryFactory jpaQueryFactory;

    QStudentInf qStudentInf = QStudentInf.studentInf;
    QClazzInf qClazzInf = QClazzInf.clazzInf;

    @Override
    public List<String> getStudentName() {
        return jpaQueryFactory.select(qStudentInf.name)
                .from(qStudentInf)
                // 按年龄降序
                .orderBy(qStudentInf.age.desc())
                .fetch();
    }

    @Override
    public List<StudentInf> getStudentInfList() {
        return jpaQueryFactory.select(qStudentInf)
                .from(qStudentInf)
                // 按学号升序：默认是升序 可以不写
                .orderBy(qStudentInf.studentId.asc())
                .fetch();
    }

    @Override
    public StudentInfDTO getStudentById(Long studentId) {
        return jpaQueryFactory.select(Projections.bean(StudentInfDTO.class, qStudentInf.studentId, qStudentInf.name,
                        qStudentInf.age, qStudentInf.address, qStudentInf.gender, qStudentInf.clazzCode))
                .from(qStudentInf)
                .where(qStudentInf.studentId.eq(studentId))
                .fetchOne();
    }

    @Override
    public List<StudentInfDTO> getStudentInfDTOList() {
        return jpaQueryFactory.select(Projections.bean(StudentInfDTO.class, qStudentInf.studentId, qStudentInf.name,
                        qStudentInf.englishName, qStudentInf.age, qStudentInf.address, qStudentInf.gender, qStudentInf.clazzCode))
                .from(qStudentInf)
                // 年龄小于于100 英文名匹配
                .where(qStudentInf.age.lt(100).and(qStudentInf.englishName.like("%a%")))
                // 先根据英文名排序 然后根据年龄排序
                .orderBy(qStudentInf.englishName.asc(), qStudentInf.age.asc())
                .fetch();
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Long updateAddress(Long studentId, String address, String englishName, Integer age) {
        return jpaQueryFactory.update(qStudentInf)
                .set(qStudentInf.address, address)
                .set(qStudentInf.englishName, englishName)
                .set(qStudentInf.age, age)
                .where(qStudentInf.studentId.eq(studentId))
                .execute();
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public Long deleteStudentInf(Long studentId) {
        return jpaQueryFactory.delete(qStudentInf)
                .where(qStudentInf.studentId.eq(studentId))
                .execute();
    }

    @Override
    public List<JoinInfDTO> getStudentInfInnerJoin() {
        return jpaQueryFactory.select(Projections.bean(JoinInfDTO.class, qClazzInf.clazzCode, qClazzInf.clazzName,
                        qStudentInf.name, qStudentInf.address))
                .from(qClazzInf)
                .innerJoin(qStudentInf)
                .on(qClazzInf.clazzCode.eq(qStudentInf.clazzCode))
                .orderBy(qStudentInf.clazzCode.desc())
                .fetch();
    }

    @Override
    public List<JoinInfDTO> getStudentInfLeftJoin() {
        return jpaQueryFactory.select(Projections.bean(JoinInfDTO.class, qClazzInf.clazzCode, qClazzInf.clazzName,
                        qStudentInf.name, qStudentInf.address))
                .from(qClazzInf)
                .leftJoin(qStudentInf)
                .on(qClazzInf.clazzCode.eq(qStudentInf.clazzCode))
                .orderBy(qStudentInf.clazzCode.desc())
                .fetch();
    }

    @Override
    public List<JoinInfDTO> getStudentInfRightJoin() {
        return jpaQueryFactory.select(Projections.bean(JoinInfDTO.class, qClazzInf.clazzCode, qClazzInf.clazzName,
                        qStudentInf.name, qStudentInf.address))
                .from(qClazzInf)
                .rightJoin(qStudentInf)
                .on(qClazzInf.clazzCode.eq(qStudentInf.clazzCode))
                .orderBy(qStudentInf.clazzCode.desc())
                .fetch();
    }

    @Override
    public List<StudentInfDTO> multiCriteriaSelect(String name, String address, Integer age) {
        return jpaQueryFactory.select(Projections.bean(StudentInfDTO.class, qStudentInf.name, qStudentInf.englishName,
                        qStudentInf.age, qStudentInf.gender, qStudentInf.address, qStudentInf.clazzCode))
                .from(qStudentInf)
                .where(
                        qStudentInf.name.like("%" + name + "%")
                        .and(qStudentInf.address.contains(address))
                        .and(qStudentInf.age.eq(age)))
                .fetch();
    }

    @Override
    public List<StudentInf> dynamicSelect(String englishName, String address, Integer age) {
        // 动态查询示例
        BooleanBuilder booleanBuilder = new BooleanBuilder();
        if (!StringUtils.isEmpty(englishName)) {
            booleanBuilder.and(qStudentInf.englishName.like("%" + englishName + "%"));
        }
        if (!StringUtils.isEmpty(address)) {
            booleanBuilder.and(qStudentInf.address.contains(address));
        }
        if (age > 0) {
            booleanBuilder.and(qStudentInf.age.eq(age));
        }
        return jpaQueryFactory
                .selectFrom(qStudentInf)
                .where(booleanBuilder)
                .fetch();
    }

    @Override
    public QueryResults<StudentInf> dynamicPageSelect(String englishName, String address, Integer age, Integer pageIndex, Integer pageSize) {
        // 动态查询示例
        BooleanBuilder booleanBuilder = new BooleanBuilder();
        // 分页对象  page:从0开始的页面索引，不能为负数  size:单页的记录条数，必须大于 0
        // Pageable封装分页信息  PageRequest提供了of()方法来创建Pageable对象
        Pageable pageable = PageRequest.of(pageIndex - 1, pageSize);
        if (!StringUtils.isEmpty(englishName)) {
            booleanBuilder.and(qStudentInf.englishName.like("%" + englishName + "%"));
        }
        if (!StringUtils.isEmpty(address)) {
            booleanBuilder.and(qStudentInf.address.contains(address));
        }
        if (age > 0) {
            booleanBuilder.and(qStudentInf.age.eq(age));
        }
        return jpaQueryFactory
                .selectFrom(qStudentInf)
                .where(booleanBuilder)
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetchResults();
    }

    // mysql聚合函数示例
    @Override
    public void aggregateFunc(Long studentId, String englishName, Integer age) {
        // avg()
        Double avgAge = jpaQueryFactory.select(qStudentInf.age.avg()).from(qStudentInf).fetchOne();
        // sum()
        Integer sumAge = jpaQueryFactory.select(qStudentInf.age.sum()).from(qStudentInf).fetchOne();
        // concat
        String concat = jpaQueryFactory.select(qStudentInf.englishName.concat(englishName)).from(qStudentInf).fetchOne();
        // contains()
        Boolean contains = jpaQueryFactory.select(qStudentInf.englishName.contains(englishName)).from(qStudentInf)
                .where(qStudentInf.studentId.eq(studentId)).fetchOne();
        // DATE_FORMAT()
        String date = jpaQueryFactory.select(Expressions.stringTemplate("DATE_FORMAT({0}, '%Y-%m-%d')",
                        qStudentInf.createTime)).from(qStudentInf).fetchOne();
    }
}
